package org.geektimes.projects.user.sql;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import org.apache.commons.lang.ClassUtils;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import java.util.function.Consumer;

/**
 * @FileName: SqlTemplate
 * @Description: sql执行模板
 * @Author: MaWenHao
 * @Created: 2021/3/1 - 下午11:32
 */
public class SqlTemplate {

    /**
     * 数据源管理器
     */
    private static final DataSourceManager DATA_SOURCE_MANAGER;

    /**
     * 数据类型与 ResultSet 方法名映射
     */
    static Map<Class, String> resultSetMethodMappings = new HashMap<>();

    static Map<Class, String> preparedStatementMethodMappings = new HashMap<>();

    static {
        DATA_SOURCE_MANAGER = new DataSourceManager();

        resultSetMethodMappings.put(Long.class, "getLong");
        resultSetMethodMappings.put(String.class, "getString");

        preparedStatementMethodMappings.put(Long.class, "setLong"); // long
        preparedStatementMethodMappings.put(String.class, "setString"); //
    }

    /**
     * 执行新增和更新操作
     *
     * @param sql  modify语句
     * @param args 参数集,按顺序排列
     * @return 返回受影响的行数
     */
    public static int modify(String sql, Consumer<Throwable> throwableConsumer, Object... args) {
        try (Connection connection = DATA_SOURCE_MANAGER.getConnection();) {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                Class<?> argType = args[i].getClass();
                Class wrapperType = ClassUtils.wrapperToPrimitive(argType);
                if (Objects.isNull(wrapperType)) {
                    wrapperType = argType;
                }
                String methodName = preparedStatementMethodMappings.get(wrapperType);
                Method method = PreparedStatement.class.getMethod(methodName, int.class, wrapperType);
                method.invoke(preparedStatement, i + 1, args[i]);
            }
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            throwableConsumer.accept(e.getCause());
        }
        return 0;
    }

    // public static void main(String[] args) throws SQLException {
    //     String databaseURL = "jdbc:derby:db/user-platform;create=true";
    //     Connection connection = DriverManager.getConnection(databaseURL);
    //
    //     Statement statement = connection.createStatement();
    //     // 删除 users 表
    //     System.out.println(statement.execute(DBConnectionManager.DROP_USERS_TABLE_DDL_SQL)); // false
    //     // 创建 users 表
    //     System.out.println(statement.execute(DBConnectionManager.CREATE_USERS_TABLE_DDL_SQL)); // false
    //     System.out.println(statement.executeUpdate(DBConnectionManager.INSERT_USER_DML_SQL));  // 5
    //
    //     // 执行查询语句（DML）
    //     ResultSet resultSet = statement.executeQuery("SELECT id,name,password,email,phoneNumber FROM users");
    //
    //     while (resultSet.next()) {
    //         System.out.println(resultSet.getString("name"));
    //     }
    // }

    /**
     * 查询集合操作
     *
     * @param sql    查询sql
     * @param aClass 返回对象的Class
     * @param args   参数
     * @param <T>    返回集合元素的泛型
     * @return
     */
    public static <T> List<T> selectList(String sql, Class<T> aClass, Object... args) {
        try (Connection connection = DATA_SOURCE_MANAGER.getConnection();) {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                Class<?> argType = args[i].getClass();
                Class wrapperType = ClassUtils.wrapperToPrimitive(argType);
                if (Objects.isNull(wrapperType)) {
                    wrapperType = argType;
                }
                String methodName = preparedStatementMethodMappings.get(wrapperType);
                Method method = PreparedStatement.class.getMethod(methodName, int.class, wrapperType);
                method.invoke(preparedStatement, i + 1, args[i]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            return SqlTemplate.convert(resultSet, aClass);
        } catch (Exception e) {
            // throwableConsumer.accept(e.getCause());
        }
        return null;
    }

    private static <T> List<T> convert(ResultSet resultSet, Class<T> aClass) throws Exception {
        List<T> resultList = new ArrayList<>();
        BeanInfo beanInfo = Introspector.getBeanInfo(aClass, Object.class);
        while (resultSet.next()) {
            T obj = aClass.newInstance();
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
                String name = propertyDescriptor.getName();
                Class<?> type = propertyDescriptor.getPropertyType();
                // 获取类属性对应的值
                String getterMethodName = resultSetMethodMappings.get(type);
                Method getterMethod = ResultSet.class.getMethod(getterMethodName, String.class);
                Object value = getterMethod.invoke(resultSet, name);
                if (ObjectUtil.isEmpty(value)) {
                    continue;
                }
                // 设置值
                Method writeMethod = propertyDescriptor.getWriteMethod();
                writeMethod.invoke(obj, value);
            }
            resultList.add(obj);
        }
        return resultList;
    }

    public static <T> T selectOne(String queryAllSql, Class<T> aClass, Object... arg) {
        List<T> resultList = SqlTemplate.selectList(queryAllSql, aClass, arg);
        if (CollectionUtil.isEmpty(resultList)) {
            return null;
        }
        return resultList.get(0);
    }
}
